﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Baby.GrowUp.Data
{
    /// <summary>
    /// 用于封装SqlDBConnection的分页结果集
    /// </summary>
    public class PageData
    {
        /// <summary>
        /// 数据总量
        /// </summary>
        public int Count { get; set; }
        /// <summary>
        /// 当前页索引
        /// </summary>
        public int Index { get; set; }
        /// <summary>
        /// 每页数据量
        /// </summary>
        public int Size { get; set; }

        /// <summary>
        /// 分页量
        /// </summary>
        public int PageCount { get; set; }

        /// <summary>
        /// 数据
        /// </summary>
        public DataTable Data { get; set; }
    }

    public class SqlDbConnection : IDisposable
    {
        #region 属性
        public enum OrderBy
        {
            /// <summary>
            /// 降序
            /// </summary>
            DESC,
            /// <summary>
            /// 升序
            /// </summary>
            ASC
        }

        private SqlConnection con = null;
        private SqlCommand com = null;
        private SqlDataReader red = null;
        private SqlTransaction tran = null;

        /// <summary>
        /// 获取或设置查询语句
        /// </summary>
        public string CommandText { get; set; }
        #endregion

        #region 初始化
        /// <summary>
        /// 链接到默认数据库
        /// </summary>
        /// <returns></returns>
        public static SqlDbConnection Connect()
        {
            ConnectionStringSettings conSet = ConfigurationManager.ConnectionStrings["Baby-GrowUp"];
            if (conSet != null)
            {
                return new SqlDbConnection(conSet);
            }

            throw new Exception("找不到数据库链接配置项HidistroSqlServer，无法链接数据库，请检查Web.config");
        }

        private SqlDbConnection(ConnectionStringSettings conSet)
        {
            con = new SqlConnection(conSet.ConnectionString);
            con.Open();
            com = new SqlCommand()
            {
                Connection = con
            };
        }
        #endregion

        #region 参数
        /// <summary>
        /// 添加查询参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void AddParameters(string name, object value)
        {
            com.Parameters.Add(new SqlParameter(name, value ?? DBNull.Value));
        }

        /// <summary>
        /// 清除参数
        /// </summary>
        public void ClearParameters()
        {
            com.Parameters.Clear();
        }
        #endregion

        #region 事务处理
        /// <summary>
        /// 开始事物
        /// </summary>
        /// <param name="iso"></param>
        public void BeginTransaction(IsolationLevel iso)
        {
            tran = this.con.BeginTransaction(iso);
            com.Transaction = this.tran;
        }

        /// <summary>
        /// 开始事物
        /// </summary>
        public void BeginTransaction()
        {
            tran = this.con.BeginTransaction();
            com.Transaction = this.tran;
        }

        /// <summary>
        /// 提交事物
        /// </summary>
        public void CommitTransaction()
        {
            if (tran == null)
            {
                throw new Exception("在未启用事务的上下文中，无法提交事务");
            }
            if (red != null && !this.red.IsClosed) { red.Close(); }
            tran.Commit();
            tran = null;
        }

        /// <summary>
        /// 回滚事物
        /// </summary>
        public void RollbackTransaction()
        {
            if (tran == null)
            {
                throw new Exception("在未启用事务的上下文中，无法回滚事务");
            }
            if (red != null && !this.red.IsClosed) { red.Close(); }
            tran.Rollback();
            tran = null;
        }
        #endregion

        #region 执行语句
        /// <summary>
        /// 执行无返回结果集的语句,将返回语句所影响的数据行数
        /// </summary>
        /// <returns></returns>
        public int Exec()
        {

            com.CommandText = CommandText;
            int result = com.ExecuteNonQuery();
            com.Parameters.Clear();
            return result;

        }

        /// <summary>
        /// 执行返回结果集的语句
        /// </summary>
        /// <returns></returns>
        public DataSet Query()
        {
            com.CommandText = CommandText;
            SqlDataAdapter sda = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            com.Parameters.Clear();
            return ds;
        }

        /// <summary>
        /// 返回第一行第一列
        /// </summary>
        /// <returns></returns>
        public Object ExecuteScalar()
        {
            com.CommandText = CommandText;
            var ret = com.ExecuteScalar();
            com.Parameters.Clear();
            return ret;
        }

        /// <summary>
        /// 执行返回DataReader的语句.
        /// 注意!不可将DataReader作为返回值超出SqlDBConnection所在的上下文返回,否则DataReader将被回收
        /// </summary>
        /// <returns></returns>
        public SqlDataReader Reader()
        {
            com.CommandText = CommandText;
            red = com.ExecuteReader();
            com.Parameters.Clear();
            return red;

        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="index">页索引 第一页为1</param>
        /// <param name="size">每页数据量</param>
        /// <param name="orderByColumn">排序字段名</param>
        /// <param name="orderby">排序规则</param>
        /// <returns></returns>
        public PageData Pageing(int index, int size, string orderByColumn, OrderBy orderby)
        {

            string sql = @"

select count(*) as [count]  from ({2}) as o;
select top {0} * from 
(
	select row_number() over(order by {1}) as rownumber,
	* from 
    (
        {2}
    ) as a
) as b 
where rownumber > {3} 
order by {1};";
            string obStr = orderby == OrderBy.ASC ? orderByColumn + " asc " : orderByColumn + " desc ";

            com.CommandText = string.Format(sql, size, obStr, CommandText, (index - 1) * size);
            SqlDataAdapter sda = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            com.Parameters.Clear();

            int dataCount = (int)ds.Tables[0].Rows[0]["count"];
            int pageCount = (dataCount / size) + (dataCount % size > 0 ? 1 : 0);


            return new PageData()
            {
                PageCount = pageCount,
                Index = index,
                Size = size,
                Count = dataCount,
                Data = ds.Tables[1]
            };

        }
        #endregion

        #region 对象辅助函数
        /// <summary>
        /// 将查询语句所返回的数据的第一个DataTable的第一行数据转换为指定类型
        /// </summary>
        /// <typeparam name="T">带有无参构造函数的类型</typeparam>
        /// <returns></returns>
        public T ToObject<T>() where T : class
        {
            DataSet ds = Query();
            if (ds.Tables.Count == 0) { return null; }
            DataTable tab = ds.Tables[0];
            if (tab.Rows.Count == 0) { return null; }
            return tab.Rows[0].ToObject<T>();

        }

        /// <summary>
        /// 将查询语句所返回的数据的第一个DataTable的第一行数据填充到指定对象
        /// </summary>
        /// <typeparam name="T">带有无参构造函数的类型</typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public T FillObject<T>(T obj) where T : class
        {
            DataSet ds = this.Query();
            if (ds.Tables.Count == 0) { return null; }
            DataTable tab = ds.Tables[0];
            if (tab.Rows.Count == 0) { return null; }
            return tab.Rows[0].FillObject(obj);

        }

        /// <summary>
        /// 将查询语句所返回的数据的第一个DataTable的数据转换为对象
        /// </summary>
        /// <typeparam name="T">带有无参构造函数的类型</typeparam>
        /// <returns></returns>
        public T[] ToObjectList<T>() where T : class
        {
            return this.ToObjectList<T>(0);
        }


        /// <summary>
        /// 将查询语句所返回的数据的第一个DataTable的数据转换为对象
        /// </summary>
        /// <typeparam name="T">带有无参构造函数的类型</typeparam>
        /// <param name="tableIndex">DataSet.Tables[tableIndex]</param>
        /// <returns></returns>
        public T[] ToObjectList<T>(int tableIndex) where T : class
        {
            DataSet ds = this.Query();
            if (ds.Tables.Count <= tableIndex) { return new T[0]; }
            return ds.Tables[tableIndex].ToObject<T>();

        }




        #endregion

        #region Dispose
        public void Dispose()
        {
            try
            {
                if (red != null)
                {
                    red.Close();
                }
            }
            catch
            {
            }

            try
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            catch
            {
            }

            try
            {

                if (con != null)
                {
                    con.Close();
                }
            }
            catch
            {

            }
        }
        #endregion
    }
}
